package com.yang.dao;

import com.yang.pojo.Category;
import com.yang.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CategoryDao {
    public ArrayList<Category> selectCategory() throws SQLException {
        //查询所有分类商品的信息
        ArrayList<Category> categoryList = new ArrayList<>();
        String sql = "SELECT category_id categoryId,category_name categoryName,category_icon categoryIcon,category_status categoryStatus FROM tb_category";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        categoryList = (ArrayList<Category>) queryRunner.query(sql, new BeanListHandler<Category>(Category.class));
        return categoryList;
    }

    //根据id删除category中的某一个分类
    public int deleteCategoryById(int categoryId) throws SQLException {
        int i = 0;
        String sql = "DELETE FROM tb_category WHERE category_id=?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, categoryId);

        return i;
    }

    //根据分类id查询分类信息
    public Category selectCategoryById(int categoryId) throws SQLException {

        String sql = "SELECT category_id categoryId,category_name categoryName,category_icon categoryIcon,category_status categoryStatus FROM tb_category where category_id=?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Category category = queryRunner.query(sql, new BeanHandler<Category>(Category.class),categoryId);
        return category;
    }

    //根据分类Id修改分类信息,修改名称和图标   （对于分类的状态，通过启用和停用来实现）
    public int updateCategoryById(String categoryName,String categoryIcon,int categoryId) throws SQLException {
        int i = 0;
        String sql = "UPDATE tb_category SET category_name=?,category_icon=? WHERE category_id=?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, categoryName, categoryIcon, categoryId);
        return i;
    }

    //添加分类信息
    public int insertCategory(String categoryName,String categoryIcon) throws SQLException {
        int i = 0;
        String sql = "insert into tb_category(category_name,category_icon,category_status) values (?,?,1)";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, categoryName, categoryIcon);
        return i;

    }

    //根据分类id修改分类状态
    public int updateCategoryStatus(String categoryStatus,int categoryId) throws SQLException {
        int i=0;
        String sql = "update tb_category set category_status=? where category_id=?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, categoryStatus, categoryId);
        return i;
    }

}
